#!/use/bin/python
# coding=utf-8
# 记录 订阅服务的设备到redis上 无需执行一次
#KEY
# LOG_DIVICE_ACTIVE_V2_* 为服务当前总订阅量  LOG_DIVICE_NEW_ACTIVE_V2_* 为服务当天订阅量
# LOG_DIVICE_ACTIVE_V2_ONCE_ZJKJ 曾经订阅过早间服务的设备激活id LOG_DIVICE_ACTIVE_V2_RETENTION_ZJKJ
import redis
import datetime
import pymysql

from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur


# redis
def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r


# 记录 早间开机每天订阅的设备 到redis 中
def saveZjkjActive(date):
    try:
        # 获取当日早间服务订阅数量
        conn, cur = getConfConnection()
        r = getReidsConnection()
        selectNumSql = """
                    select t.active_id from life_assistant_device t where t.zjkj = 1 and t.zjkj_ui =1
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchall()
        key = "LOG_DIVICE_ACTIVE_V2_ZJKJ_"+date
        for result in resultNum:
            r.sadd(key, result[0])
        print('向%s key中增加了%s条数据' % (key, len(resultNum)))
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 健康作息每天订阅的设备 到redis 中
def saveJkzxActive(date):
    try:
        # 获取当日早间服务订阅数量
        conn, cur = getConfConnection()
        r = getReidsConnection()
        selectNumSql = """
                    select t.active_id from life_assistant_device t where t.jkzx = 1 and t.jkzx_ui =1 
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchall()
        key = "LOG_DIVICE_ACTIVE_V2_JKZX_"+date
        for result in resultNum:
            r.sadd(key, result[0])
        print('向%s key中增加了%s条数据' % (key, len(resultNum)))
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 夜间音量早间开机每天订阅的设备 到redis 中
def saveYjylActive(date):
    try:
        # 获取当日早间服务订阅数量
        conn, cur = getConfConnection()
        r = getReidsConnection()
        selectNumSql = """
                    select t.active_id from life_assistant_device t where t.yjyl = 1 and t.yjyl_ui =1 
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchall()
        key = "LOG_DIVICE_ACTIVE_V2_YJYL_"+date
        for result in resultNum:
            r.sadd(key, result[0])
        print('向%s key中增加了%s条数据' % (key, len(resultNum)))
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 夜间音量每天订阅的设备 到redis 中
def saveYjylActive(date):
    try:
        # 获取当日早间服务订阅数量
        conn, cur = getConfConnection()
        r = getReidsConnection()
        selectNumSql = """
                    select t.active_id from life_assistant_device t where t.yjyl = 1 and t.yjyl_ui =1 
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchall()
        key = "LOG_DIVICE_ACTIVE_V2_YJYL_"+date
        for result in resultNum:
            r.sadd(key, result[0])
        print('向%s key中增加了%s条数据' % (key, len(resultNum)))
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 追剧提醒每天订阅的设备 到redis 中
def saveZjtxActive(date):
    try:
        # 获取当日追剧提醒订阅数量
        conn, cur = getConfConnection()
        r = getReidsConnection()
        selectNumSql = """
                    select t.active_id from life_assistant_device t where t.zjtx = 1 and t.zjtx_ui =1 
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchall()
        key = "LOG_DIVICE_ACTIVE_V2_ZJTX_"+date
        for result in resultNum:
            r.sadd(key, result[0])
        print('向%s key中增加了%s条数据' % (key, len(resultNum)))
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 生活助手(三个服务任意一个)每天订阅的设备 到redis 中
def saveAllActive(date):
    try:
        # 获取当日早间服务订阅数量
        conn, cur = getConfConnection()
        r = getReidsConnection()
        selectNumSql = """
                    select t.active_id from life_assistant_device t where (t.zjkj = 1 and t.zjkj_ui =1 ) 
                        or (t.jkzx = 1 and t.jkzx_ui =1 ) or (t.yjyl = 1 and t.yjyl_ui =1 )
                """;
        cur.execute(selectNumSql)
        resultNum = cur.fetchall()
        key = "LOG_DIVICE_ACTIVE_V2_ALL_"+date
        for result in resultNum:
            r.sadd(key, result[0])
        print('向%s key中增加了%s条数据' % (key, len(resultNum)))
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 早间开机每天新增订阅的设备 到redis 中
def saveZjkjNewActive(toDay,beforeDay):
    r = getReidsConnection()
    todoKey = "LOG_DIVICE_ACTIVE_V2_ZJKJ_"+toDay
    beforeKey = "LOG_DIVICE_ACTIVE_V2_ZJKJ_"+beforeDay
    newKey = "LOG_DIVICE_NEW_ACTIVE_V2_ZJKJ_"+date
    # 取 在当日订阅设备 但不在昨日的订阅设备中的 设备 作为当日的新增设备 存入redis
    r.sdiffstore(newKey, todoKey,beforeKey)
    todoNun = r.scard(todoKey)
    beforeNun = r.scard(beforeKey)
    newNun = r.scard(newKey)
    logDetail = '早间开机-今日累计订阅(无需执行一次)设备%s,昨日累计订阅设备%s,今日新增订阅设备%s'% (todoNun,beforeNun,newNun)
    print(logDetail)
    saveLog(logDetail, date)

# 记录 健康作息每天新增订阅的设备 到redis 中
def saveJkzxNewActive(toDay,beforeDay):
    r = getReidsConnection()
    todoKey = "LOG_DIVICE_ACTIVE_V2_JKZX_"+toDay
    beforeKey = "LOG_DIVICE_ACTIVE_V2_JKZX_"+beforeDay
    newKey = "LOG_DIVICE_NEW_ACTIVE_V2_JKZX_"+date
    # 取 在当日订阅设备 但不在昨日的订阅设备中的 设备 作为当日的新增设备 存入redis
    r.sdiffstore(newKey, todoKey,beforeKey)
    todoNun = r.scard(todoKey)
    beforeNun = r.scard(beforeKey)
    newNun = r.scard(newKey)
    logDetail ='健康作息-今日累计订阅(无需执行一次)设备%s,昨日累计订阅设备%s,今日新增订阅设备%s'% (todoNun,beforeNun,newNun)
    print(logDetail)
    saveLog(logDetail, date)

# 记录 夜间音量每天新增订阅的设备 到redis 中
def saveYjylNewActive(toDay,beforeDay):
    r = getReidsConnection()
    todoKey = "LOG_DIVICE_ACTIVE_V2_YJYL_"+toDay
    beforeKey = "LOG_DIVICE_ACTIVE_V2_YJYL_"+beforeDay
    newKey = "LOG_DIVICE_NEW_ACTIVE_V2_YJYL_"+date
    # 取 在当日订阅设备 但不在昨日的订阅设备中的 设备 作为当日的新增设备 存入redis
    r.sdiffstore(newKey, todoKey,beforeKey)
    todoNun = r.scard(todoKey)
    beforeNun = r.scard(beforeKey)
    newNun = r.scard(newKey)
    logDetail ='夜间音量-今日累计订阅(无需执行一次)设备%s,昨日累计订阅设备%s,今日新增订阅设备%s'% (todoNun,beforeNun,newNun)
    print(logDetail)
    saveLog(logDetail, date)

# 记录 追剧提醒每天新增订阅的设备 到redis 中
def saveZjtxNewActive(toDay,beforeDay):
    r = getReidsConnection()
    todoKey = "LOG_DIVICE_ACTIVE_V2_ZJTX_"+toDay
    beforeKey = "LOG_DIVICE_ACTIVE_V2_ZJTX_"+beforeDay
    newKey = "LOG_DIVICE_NEW_ACTIVE_V2_ZJTX_"+date
    # 取 在当日订阅设备 但不在昨日的订阅设备中的 设备 作为当日的新增设备 存入redis
    r.sdiffstore(newKey, todoKey,beforeKey)
    todoNun = r.scard(todoKey)
    beforeNun = r.scard(beforeKey)
    newNun = r.scard(newKey)
    logDetail ='追剧提醒-今日累计订阅(无需执行一次)设备%s,昨日累计订阅设备%s,今日新增订阅设备%s'% (todoNun,beforeNun,newNun)
    print(logDetail)
    saveLog(logDetail, date)

# 记录 生活助手(三个服务任意一个)每天新增订阅的设备 到redis 中
def saveAllNewActive(toDay,beforeDay):
    r = getReidsConnection()
    todoKey = "LOG_DIVICE_ACTIVE_V2_ALL_"+toDay
    beforeKey = "LOG_DIVICE_ACTIVE_V2_ALL_"+beforeDay
    newKey = "LOG_DIVICE_NEW_ACTIVE_V2_ALL_"+date
    # 取 在当日订阅设备 但不在昨日的订阅设备中的 设备 作为当日的新增设备 存入redis
    r.sdiffstore(newKey, todoKey,beforeKey)
    todoNun = r.scard(todoKey)
    beforeNun = r.scard(beforeKey)
    newNun = r.scard(newKey)
    logDetail = '生活助手(三个服务任意一个)-今日累计订阅(无需执行一次)设备%s,昨日累计订阅设备%s,今日新增订阅设备%s'% (todoNun,beforeNun,newNun)
    print(logDetail)
    saveLog(logDetail, date)

# 记录 截止7天前曾经订阅过的设备
# 记录 从新增 后7天 仍然订阅的设备
# toDay 日期 serId: 服务id
def saveOnceAndRetentionActive(toDay,serId):
    r = getReidsConnection()
    #前7天日期
    day7 = strToTime(toDay, -7)
    day7Key = f"LOG_DIVICE_ACTIVE_V2_{serId}_{day7}"
    day7KeyNum = r.scard(day7Key)
    onceKey = f'LOG_DIVICE_ACTIVE_V2_ONCE_{serId}'
    onceKeyNum = r.scard(onceKey)
    #将7天前的订阅激活id 加入到 "截止7天前曾经订阅过的设备"中
    r.sunionstore(onceKey, day7Key, onceKey)
    newOnceNum = r.scard(onceKey)
    # 获取符合7天留存的设备量
    day7NewKey = f"LOG_DIVICE_NEW_ACTIVE_V2_{serId}_{day7}"
    day7NewKeyNum = r.scard(day7NewKey)
    toDaykey = f"LOG_DIVICE_ACTIVE_V2_{serId}_{toDay}"
    toDaykeyNum = r.scard(toDaykey)
    retentionKey = f'LOG_DIVICE_ACTIVE_V2_RETENTION_{serId}'
    retentionKeyNum = r.scard(retentionKey)
    newTempKey = f'LOG_DIVICE_ACTIVE_V2_TEMP_{serId}'
    # N日且N+7日服务处于打开的设备
    r.sinterstore(newTempKey, toDaykey, day7NewKey)
    r.sunionstore(retentionKey, newTempKey, retentionKey)
    newRetentionNum = r.scard(retentionKey)
    serName = serIdToName(serId)
    logDetail = f'{toDay}日期{serName}-7天前总订阅数为{day7KeyNum},截止7天前曾经订阅过的设备数量{onceKeyNum},取交集后数量为{newOnceNum},' \
                f'7天前新增订阅数为{day7NewKeyNum},当天总订阅数为{toDaykeyNum},7日留存总量为{retentionKeyNum},更新后为{newRetentionNum}'
    print(logDetail)
    saveLog(logDetail, date)

#根据serId 获取服务中文名
def serIdToName(serId):
    if serId =='ZJKJ':
        serName = "早间开机"
    elif serId =='JKZX':
        serName = "健康作息"
    elif serId =='YJYL':
        serName = "夜间音量"
    elif serId =='ZJTX':
        serName = "追剧提醒"
    else:
        serName = "其他"
    return serName

#将指定字符串 dateStr ,偏移 offsetInt 天后再输出字符串
#如 20220805 偏移7天 即增加7天后 输出 20220812
def strToTime(dateStr,offsetInt):
    t = datetime.datetime.strptime(dateStr, "%Y%m%d")
    #print(t)
    offset = datetime.timedelta(days=offsetInt)
    re_date = (t+offset).strftime('%Y%m%d')
    #print(re_date)
    return  re_date

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 41
        jobName = '	日志-将每日订阅的设备(无需执行一次)激活id存入redis'
        tableName = ''
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    beforeDate = getdate(2)
    #today = "2021-12-13"
    print ("%s 日期,记录 订阅服务(无需执行一次)的设备到redis上" % date)
    saveZjkjActive(date)
    saveJkzxActive(date)
    saveYjylActive(date)
    saveZjtxActive(date)
    saveAllActive(date)
    saveZjkjNewActive(date,beforeDate)
    saveJkzxNewActive(date,beforeDate)
    saveYjylNewActive(date,beforeDate)
    saveZjtxNewActive(date,beforeDate)
    saveAllNewActive(date,beforeDate)
    saveOnceAndRetentionActive(date,'ZJKJ')
    saveOnceAndRetentionActive(date,'YJYL')
    saveOnceAndRetentionActive(date,'JKZX')
    saveOnceAndRetentionActive(date,'ZJTX')
